/****** Object:  StoredProcedure [dbo].[sp_po_futures_order_move]    Script Date: 11/11/2009 13:20:22 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_po_futures_order_move]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_po_futures_order_move]
GO
/****** Object:  StoredProcedure [dbo].[sp_po_futures_order_move]    Script Date: 11/11/2009 13:18:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************************
* sp Name: dbo.sp_po_futures_order_move
* Created: 20100806, RGM
* Purpose: determina el ID de la purchase_order_version superior o inferior
*	dado el ordenamiento para entonces invertir los valores
*
* Modified: 20100820 RGM, Se corrige el ordenamiento en los movimientos UP
*		para invertirlo y obtener en el TOP 1 el correcto
*	20100820 RGM, se quita la condicional de que solo fuera maiz importado
*
* HistoryDate.
* yyyymmdd User, data
* 
* Note: nothing
* 
***********************************************************************/
CREATE PROCEDURE [dbo].[sp_po_futures_order_move]
	@purchaseOrderVersionId1	INT, 
	@direction					INT, 
	@DEBUG						INT = 0
AS

/*
	error handling
*/
BEGIN TRY
	DECLARE
		@TranStarted BIT
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 ) BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END ELSE BEGIN
		SET @TranStarted = 0
	END

	SET NOCOUNT ON
-------------------------------------------------------
--[BEGIN] SP section
DECLARE @text VARCHAR(512)

SET @text = '	------ Ejecutando sp_po_futures_order_move ' 
		+ CONVERT(VARCHAR(10), @purchaseOrderVersionId1) + ', ' + 
		+ CONVERT(VARCHAR(10), @direction)

IF @DEBUG = 1
	PRINT @text

DECLARE 
	@purchaseOrderVersionId2	INT, 

-- manejo de entorno
	@RCOUNT					INT

-- se decide cual funcion usar dado el ordenamiento
-- direction = 1 se UP (buscar al anterior para invertir)
IF @direction = 1 BEGIN
	SELECT TOP 1 @purchaseOrderVersionId2 = pov.purchase_order_version_id
		FROM purchases_orders_versions pov, vw_periods_versions pv, transports t, 
			corns_types ct, purchases_orders_versions pov2, vw_periods_versions pv2
		WHERE pov.biweek_period_version_id = pv.biweek_period_version_id
			AND pov.transport_id = t.transport_id
			AND pov.corn_type_id = ct.corn_type_id
			AND pov.is_transit = 0
--			AND ct.corn_origin_id = 'I'
			AND pov2.biweek_period_version_id = pv2.biweek_period_version_id
			AND pv.future_period_version_id = pv2.future_period_version_id
			AND pov.futures_order < pov2.futures_order
			AND pov2.purchase_order_version_id = @purchaseOrderVersionId1
		ORDER BY pov.futures_order DESC, pov.biweek_period_version_id DESC, 
			t.priority ASC, pov.code DESC, pov.code_number DESC

	IF @DEBUG = 1
		PRINT 'N:valor anterior [' + CONVERT(VARCHAR(10), ISNULL(@purchaseOrderVersionId2, 0)) + ']'
END

-- direction = 2 se DOWN (buscar al posterior para invertir)
IF @direction = 2 BEGIN
	SELECT TOP 1 @purchaseOrderVersionId2 = pov.purchase_order_version_id
		FROM purchases_orders_versions pov, vw_periods_versions pv, transports t, 
			corns_types ct, purchases_orders_versions pov2, vw_periods_versions pv2
		WHERE pov.biweek_period_version_id = pv.biweek_period_version_id
			AND pov.transport_id = t.transport_id
			AND pov.corn_type_id = ct.corn_type_id
			AND pov.is_transit = 0
--			AND ct.corn_origin_id = 'I'
			AND pov2.biweek_period_version_id = pv2.biweek_period_version_id
			AND pv.future_period_version_id = pv2.future_period_version_id
			AND pov.futures_order > pov2.futures_order
			AND pov2.purchase_order_version_id = @purchaseOrderVersionId1
		ORDER BY pov.futures_order, pov.biweek_period_version_id, 
			t.priority DESC, pov.code, pov.code_number

	IF @DEBUG = 1
		PRINT 'N:valor posterior [' + CONVERT(VARCHAR(10), ISNULL(@purchaseOrderVersionId2, 0)) + ']'
END

IF @purchaseOrderVersionId2 IS NOT NULL BEGIN
	EXEC sp_po_futures_order_invert @purchaseOrderVersionId1, @purchaseOrderVersionId2, @DEBUG
END

IF @DEBUG = 1
	PRINT 'END:' + @text

--[END] SP section
-------------------------------------------------------
	SET NOCOUNT OFF

	IF( @TranStarted = 1 ) BEGIN
		SET @TranStarted = 0
		COMMIT TRANSACTION
	END

	RETURN 1 --simil boleano a true
END TRY BEGIN CATCH
	DECLARE 
		@ErrorMessage	NVARCHAR(4000), 
		@ErrorNumber	INT, 
		@ErrorSeverity	INT, 
		@ErrorState		INT

	SELECT @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorNumber = ERROR_NUMBER()

	IF( @TranStarted = 1 ) BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
	RETURN 0 --simil boleano a mal
END CATCH 



